using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using DevExpress.XtraReports.UI;
using ProgNetComponents.Data.SqlCe;
using System.Collections.Generic;
using System.Globalization;
using System.Data;
using ProgNet.Constants;
using ProgNetDxComponents.ExtenderProviders.Browsers;
using MiniKsiegowosc.RaportTools;

namespace MiniKsiegowosc.Wrk.Ksiegi.VatS
{
    public partial class raportVatS : MonthReportBase
    {
        public raportVatS()
        {
            InitializeComponent();
        }

        protected override string GetSelectString()
        {
            string SELECT = "select  " +
                            "W.WRK_VATS_NUMER AS NUMER, " +
                            "W.WRK_VATS_DATA_WYSTAWIENIA AS DATA_WYSTAWIENIA, " +
                            "W.WRK_VATS_DATA_SPRZEDAZY AS DATA_SPRZEDAZY, " +
                            "K.STP_KON_NAZWA AS NABYWCA_NAZWA, " +
                            "K.STP_KON_ADRES AS NABYWCA_ADRES, " +
                            "K.STP_KON_NIP AS NABYWCA_NIP, " +
                            "COALESCE(SUM(K1.WRK_VATSK_KWOTA_NETTO * K1.WRK_VATSK_ILOSC), 0) AS KWOTA_N, " +
                            "COALESCE(SUM(K2.WRK_VATSK_KWOTA_NETTO * K2.WRK_VATSK_ILOSC), 0) AS KWOTA_ZW, " +
                            "COALESCE(SUM(K3.WRK_VATSK_KWOTA_NETTO * K3.WRK_VATSK_ILOSC), 0) AS KWOTA_S0, " +
                            "COALESCE(SUM(K4.WRK_VATSK_KWOTA_NETTO * K4.WRK_VATSK_ILOSC), 0) AS KWOTA_S1, " +
                            "COALESCE(SUM(K4.WRK_VATSK_KWOTA_NETTO * K4.WRK_VATSK_ILOSC * R1.STP_LP_VAT_S1), 0) AS KWOTA_VAT_S1, " +
                            "COALESCE(SUM(K5.WRK_VATSK_KWOTA_NETTO * K5.WRK_VATSK_ILOSC), 0) AS KWOTA_S2, " +
                            "COALESCE(SUM(K5.WRK_VATSK_KWOTA_NETTO * K5.WRK_VATSK_ILOSC * R1.STP_LP_VAT_S2), 0) AS KWOTA_VAT_S2, " +
                            "COALESCE(SUM(K6.WRK_VATSK_KWOTA_NETTO * K6.WRK_VATSK_ILOSC), 0) AS KWOTA_S3, " +
                            "COALESCE(SUM(K6.WRK_VATSK_KWOTA_NETTO * K6.WRK_VATSK_ILOSC * R1.STP_LP_VAT_S3), 0) AS KWOTA_VAT_S3, " +
                            "COALESCE(SUM(K7.WRK_VATSK_KWOTA_NETTO * K7.WRK_VATSK_ILOSC), 0) AS KWOTA_WEW, " +
                            "COALESCE(SUM(K8.WRK_VATSK_KWOTA_NETTO * K8.WRK_VATSK_ILOSC), 0) AS KWOTA_EXP " +
                            "FROM WRK_VATS W  " +
                            "LEFT JOIN STP_KONTRACHENCI K ON W.WRK_VATS_NABYWCA = K.STP_KON_ID " +
                            "LEFT OUTER JOIN WRK_VATS_KWOTY K1 ON K1.WRK_VATSK_FAKTURA = W.WRK_VATS_ID AND K1.WRK_VATSK_TYP = 'N' " +
                            "LEFT OUTER JOIN WRK_VATS_KWOTY K2 ON K2.WRK_VATSK_FAKTURA = W.WRK_VATS_ID AND K2.WRK_VATSK_TYP = 'ZW' " +
                            "LEFT OUTER JOIN WRK_VATS_KWOTY K3 ON K3.WRK_VATSK_FAKTURA = W.WRK_VATS_ID AND K3.WRK_VATSK_TYP = 'S0' " +
                            "LEFT OUTER JOIN WRK_VATS_KWOTY K4 ON K4.WRK_VATSK_FAKTURA = W.WRK_VATS_ID AND K4.WRK_VATSK_TYP = 'S1' " +
                            "LEFT OUTER JOIN WRK_VATS_KWOTY K5 ON K5.WRK_VATSK_FAKTURA = W.WRK_VATS_ID AND K5.WRK_VATSK_TYP = 'S2' " +
                            "LEFT OUTER JOIN WRK_VATS_KWOTY K6 ON K6.WRK_VATSK_FAKTURA = W.WRK_VATS_ID AND K6.WRK_VATSK_TYP = 'S3' " +
                            "LEFT OUTER JOIN WRK_VATS_KWOTY K7 ON K7.WRK_VATSK_FAKTURA = W.WRK_VATS_ID AND K7.WRK_VATSK_TYP = 'WEW' " +
                            "LEFT OUTER JOIN WRK_VATS_KWOTY K8 ON K8.WRK_VATSK_FAKTURA = W.WRK_VATS_ID AND K8.WRK_VATSK_TYP = 'EXP' " +
                            "LEFT OUTER JOIN STP_ROK_PODATKOWY R1 ON R1.STP_LP_ROK = " + Rok + " " +
                            "WHERE DATEPART(YEAR, W.WRK_VATS_DATA_WYSTAWIENIA ) = " + Rok + " AND DATEPART(MONTH, W.WRK_VATS_DATA_WYSTAWIENIA ) = " + Miesiac + " " +
                            "GROUP BY  " +
                            "W.WRK_VATS_NUMER, " +
                            "W.WRK_VATS_DATA_WYSTAWIENIA , " +
                            "W.WRK_VATS_DATA_SPRZEDAZY, " +
                            "K.STP_KON_NAZWA, " +
                            "K.STP_KON_ADRES, " +
                            "K.STP_KON_NIP ";
            return SELECT;
        }

        public override void GetData()
        {

            DataRow row = BrowserCache.GetRowForValue(BrowserNames.RokPodatkowy, Rok);

            this.pS1.Value = row["STP_LP_VAT_S1"].AsDecimal();
            this.pS2.Value = row["STP_LP_VAT_S2"].AsDecimal();
            this.pS3.Value = row["STP_LP_VAT_S3"].AsDecimal();
            base.GetData();
        }
        
    }
}
